package jp.bsws.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jp.bsws.bean.KeyValueBean;
import jp.bsws.common.BSWSUtils;
import jp.bsws.model.GroupModel;
import jp.bsws.model.JugyoinModel;
import jp.bsws.model.KengenModel;
import jp.bsws.model.TenpoModel;
import jp.bsws.model.TenpoRelationModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class BSWSDao {
	private static BSWSDao BSWSDao = new BSWSDao();

	/** ログです。 */
	private static Log log = LogFactory.getLog(BSWSDao.class);

	private static String SQL_LOGIN_USER =
		" SELECT * FROM M_JUGYOIN WHERE JUGYOIN_COD = ? AND PASSWORD = ? AND ? BETWEEN NYUTEN_YMD AND TAITEN_YMD ";

	private String SQL_GROUP = " SELECT * FROM M_GROUP ";
	private String SQL_TENPO = " SELECT * FROM M_TENPO ";
	private String SQL_JUGYOIN = " SELECT * FROM M_JUGYOIN ";

	/**
	 * コンストラクタです。
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static BSWSDao getInstance(){
		return BSWSDao;
	}

	/**
	 * ログイン情報取得
	 */
	public JugyoinModel getLoginJoho(Connection con, String strMailAddress,String strPasswd, String sansyoYmd) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_LOGIN_USER);
		stmt.setString(1, strMailAddress);
		stmt.setString(2, strPasswd);
		stmt.setInt(3, Integer.parseInt(sansyoYmd));
		ResultSet rs = stmt.executeQuery();
		JugyoinModel jugyoinModel = null;
		while(rs.next()){
			jugyoinModel = new JugyoinModel();
			jugyoinModel.setSeq(rs.getInt("SEQ"));
			jugyoinModel.setStrGroupCod(rs.getString("GROUP_COD"));
			jugyoinModel.setStrTenpoCod(rs.getString("TENPO_COD"));
			jugyoinModel.setStrJugyoinCod(rs.getString("JUGYOIN_COD"));
			jugyoinModel.setStrJugyoinKnj(rs.getString("JUGYOIN_KNJ"));
			jugyoinModel.setIntKengenSeq(rs.getInt("KENGEN_SEQ"));
		}

		rs.close();
		stmt.close();

		return jugyoinModel;
	}

	/**
	 * グループ情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public ArrayList<GroupModel> getGroupJoho(Connection con, String pkGroupCod) throws Exception{

		ArrayList<GroupModel> resultList = new ArrayList<GroupModel>();

		//検索条件の作成
		String where = "";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}

		PreparedStatement stmt = con.prepareStatement(SQL_GROUP + where);
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + 1, (String)whereList.get(i));
		}
		ResultSet rs = stmt.executeQuery();

		GroupModel groupModel = null;
		while(rs.next()){
			groupModel = new GroupModel();
			String strGroupCod = rs.getString("GROUP_COD");
			String strGroupKnj = rs.getString("GROUP_KNJ");
			groupModel.setStrGroupCod(strGroupCod);
			groupModel.setStrGroupKnj(strGroupKnj);
			resultList.add(groupModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	/**
	 * 店舗情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public ArrayList<TenpoModel> getTenpoJoho(Connection con, Integer pkSeq, String pkGroupCod, String pkTenpoCod, String pkSansyoYmd) throws Exception{

		ArrayList<TenpoModel> resultList = new ArrayList<TenpoModel>();

		//検索条件の作成
		String whereSeq = "";
		if( pkSeq != null ){
			whereSeq = BSWSUtils.getWhereAnd(whereSeq) + " SEQ = ? ";
		}

		String where = BSWSUtils.getWhereAnd(whereSeq) + " DEL_FLG = 0 ";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		if( BSWSUtils.isNotBlankNull(pkSansyoYmd)){
			where += BSWSUtils.getWhereAnd(where) + " ? BETWEEN KAITEN_YMD AND HEITEN_YMD ";
			whereList.add(pkSansyoYmd);
		}

		PreparedStatement stmt = con.prepareStatement(SQL_TENPO + whereSeq + where);
		int seqCnt = 0;
		if(BSWSUtils.isNotBlankNull(whereSeq)){
			stmt.setInt(1, pkSeq);
			seqCnt++;
		}
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + seqCnt + 1, (String)whereList.get(i));
		}
		ResultSet rs = stmt.executeQuery();

		TenpoModel tenpoModel = null;
		while(rs.next()){
			tenpoModel = new TenpoModel();
			tenpoModel.setSeq(rs.getInt("SEQ"));
			tenpoModel.setStrGroupCod(rs.getString("GROUP_COD"));
			tenpoModel.setStrTenpoCod(rs.getString("TENPO_COD"));
			tenpoModel.setStrTenpoKnj(rs.getString("TENPO_KNJ"));
			tenpoModel.setStrJusyoKnj(rs.getString("JUSYO_KNJ"));
			tenpoModel.setIntGyoSu(rs.getInt("GYO_SU"));
			tenpoModel.setIntKaitenYmd(rs.getInt("KAITEN_YMD"));
			tenpoModel.setIntHeitenYmd(rs.getInt("HEITEN_YMD"));
			tenpoModel.setIntHyojiJun(rs.getInt("HYOJI_JUN"));
			resultList.add(tenpoModel);
		}
		rs.close();
		stmt.close();

		return resultList;
	}

	/**
	 * 従業員情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<JugyoinModel> getJugyoinJoho(Connection con, Integer pkSeq, String pkGroupCod, String pkTenpoCod,String pkJugyoinCod,String pkSansyoYmd) throws Exception{

		ArrayList<JugyoinModel> resultList = new ArrayList<JugyoinModel>();

		//検索条件の作成
		String whereSeq = "";
		if( pkSeq != null ){
			whereSeq = BSWSUtils.getWhereAnd(whereSeq) + " SEQ = ? ";
		}

		String where = "";
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += BSWSUtils.getWhereAnd(where) + " TENPO_COD = ? ";
			whereList.add(pkTenpoCod);
		}
		if( BSWSUtils.isNotBlankNull(pkJugyoinCod)){
			where += BSWSUtils.getWhereAnd(where) + " JUGYOIN_COD = ? ";
			whereList.add(pkJugyoinCod);
		}
		if( BSWSUtils.isNotBlankNull(pkSansyoYmd)){
			where += BSWSUtils.getWhereAnd(where) + " ? BETWEEN NYUTEN_YMD AND TAITEN_YMD ";
			whereList.add(pkSansyoYmd);
		}

		PreparedStatement stmt = con.prepareStatement(SQL_JUGYOIN + whereSeq+ where );
		int seqCnt = 0;
		if(BSWSUtils.isNotBlankNull(whereSeq)){
			stmt.setInt(1, pkSeq);
			seqCnt++;
		}
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + seqCnt + 1, (String)whereList.get(i));
		}

		ResultSet rs = stmt.executeQuery();
		JugyoinModel jugyoinModel = null;
		while(rs.next()){
			jugyoinModel = new JugyoinModel();
			jugyoinModel.setSeq(rs.getInt("SEQ"));
			jugyoinModel.setStrGroupCod(rs.getString("GROUP_COD"));
			jugyoinModel.setStrTenpoCod(rs.getString("TENPO_COD"));
			jugyoinModel.setStrJugyoinCod(rs.getString("JUGYOIN_COD"));
			jugyoinModel.setStrJugyoinKnj(rs.getString("JUGYOIN_KNJ"));
			jugyoinModel.setStrPassWord(rs.getString("PASSWORD"));
			jugyoinModel.setIntKengenSeq(rs.getInt("KENGEN_SEQ"));
			jugyoinModel.setIntNyutenYmd(rs.getInt("NYUTEN_YMD"));
			jugyoinModel.setIntTaitenYmd(rs.getInt("TAITEN_YMD"));
			jugyoinModel.setIntHyojiJun(rs.getInt("HYOJI_JUN"));
			resultList.add(jugyoinModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	/**
	 * 最大一意番号取得
	 */
	public Integer getMaxSeq(Connection con, String strTableName) throws Exception{

		Integer resultCod = null;

		String SQL_MAX_SEQ = " SELECT  IFNULL(MAX(SEQ),0)  MAX_SEQ FROM ";

		PreparedStatement stmt = con.prepareStatement(SQL_MAX_SEQ + strTableName );

		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			resultCod = rs.getInt("MAX_SEQ");
		}

		rs.close();
		stmt.close();

		return resultCod;
	}

	/**
	 * マスタ情報削除
	 */
	public int deleteRow(Connection con, Integer seq, String strTblName) throws Exception{

		PreparedStatement stmt = con.prepareStatement(" DELETE FROM  " + strTblName + " WHERE SEQ = ? ");
		stmt.setInt(1, seq);

		int deleteCnt = stmt.executeUpdate();

		stmt.close();

		return deleteCnt;
	}
}